package services;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.DateFormat;
import java.util.Date;
import java.util.Properties;

import org.apache.axis2.context.MessageContext;

import model.Client;
import model.Product;


public final class SalesAutomationService 
{
	private static Statement stmt;
    private static Connection conn;
    private static PreparedStatement pstmt;
	
	public Product selectProduct(int productId) 
	{
		String sql = "SELECT * FROM product WHERE productID = " + productId;
		
		ResultSet rs;
		Product p = new Product();
		
		try
		{
			rs = getStatement().executeQuery(sql);
			
			if (rs.next())
			{
	            p.setProductId(productId);
	            p.setDescription(rs.getString("description"));
	            p.setValue(rs.getDouble("value"));
	        }
			else
				p = null;
	    }
		catch (SQLException ex) 
		{ ex.printStackTrace(); }
		
		return p;
	}
	
	
	public boolean makePurchase(Product product, Client c)
	{
		String sql = "INSERT INTO ecommerce.purchaseorder(productID, orderDate, clientID) VALUES (?,?,?)";
		getStatement(sql);
		
		try
		{
			pstmt.setInt(1, product.getProductId());
            pstmt.setString(2, DateFormat.getInstance().format(new Date(System.currentTimeMillis())));
            pstmt.setInt(3, c.getClientId());
            pstmt.execute();
            return true;
		}
		catch (SQLException ex)
		{ ex.printStackTrace(); }
		
		return false;
	}
	
	
	private static Statement getStatement()
	{
		if (stmt != null)
			return stmt;
		
		try 
		{
			MessageContext msg = MessageContext.getCurrentMessageContext();
			String url = (String)msg.getParameter("jdbc.url").getValue();
			String user = (String)msg.getParameter("jdbc.user").getValue();
			String password = (String)msg.getParameter("jdbc.pwd").getValue();
					
			// Properties properties = new Properties();
			// FileInputStream input = new FileInputStream("database.properties");
			// properties.load(input);
			// input.close();
						
			// String url = properties.getProperty("jdbc.url");
			// String user = properties.getProperty("jdbc.user");
			// String password = properties.getProperty("jdbc.pwd");
	        
	        Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			stmt = conn.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
		}
		catch (ClassNotFoundException ex) 
		{ System.out.println("Driver JDBC-ODBC n�o encontrado"); }
		catch (SQLException ex) 
		{ System.out.println("Erro: " + ex.getMessage()); } 
		catch (Exception e) 
		{ System.out.println("Erro: " + e.getMessage()); }

        return stmt;
	}
	
	
	private static PreparedStatement getStatement(String sql)
	{
		if (pstmt != null)
			return pstmt;
		
		try 
		{
			MessageContext msg = MessageContext.getCurrentMessageContext();
			String url = (String)msg.getParameter("jdbc.url").getValue();
			String user = (String)msg.getParameter("jdbc.user").getValue();
			String password = (String)msg.getParameter("jdbc.pwd").getValue();
			
			// Properties properties = new Properties();
			// FileInputStream input = new FileInputStream("database.properties");
			// properties.load(input);
			// input.close();
			
			// String url = properties.getProperty("jdbc.url");
			// String user = properties.getProperty("jdbc.user");
			// String password = properties.getProperty("jdbc.pwd");
	        
	        Class.forName("com.mysql.jdbc.Driver");
			conn = DriverManager.getConnection(url, user, password);
			pstmt = conn.prepareStatement(sql);
		}
		catch (ClassNotFoundException ex) 
		{ System.out.println("Driver JDBC-ODBC n�o encontrado"); }
		catch (SQLException ex) 
		{ System.out.println("Erro: " + ex.getMessage()); } 
		catch (Exception e) 
		{ System.out.println("Erro: " + e.getMessage()); }

        return pstmt;
	}
}
